Oracle——PLSQL编程语言与游标 学习笔记

目录

  • PLSQL的基本概念
  • PLSQL的编写
  • 实例1：编写输出语句
  • 实例2：定义变量并动态赋值变量
  • PLSQL的变量推荐设置
  • 实例3：引用型变量与记录型变量
  • PLSQL的选择结构：if语句
  • 实例4：选择结构
  • PLSQL的循环结构
  • 实例5：循环结构
  • 游标的基本概念
  • 游标的创建
  • 游标的四个属性
  • 实例6：游标的基本使用
  • 游标的例外（异常）
  • 实例7：游标的例外
  • 实例8：游标的使用场景

PLSQL的基本概念

可以对SQL进行编程，比如：if...else...、for循环、存储过程、存储函数等。

开发工具

  • PL/SQL Developer
  • Oracle SQL Developer

PLSQL的编写

通用模板

1
2
3
4
5
6
7
declare
	变量、常量、光标（游标）、例外（自定义异常）
begin
	程序代码...
	exception
		异常代码...
end;

实例1：编写输出语句

编写输出语句“hello world”

1
2
3
4
5
6
set serverout on;
declare
	
begin
	dbms_output.put_line('hello world');
end;

输出结果：

1
2
3
hello world

PL/SQL 过程已成功完成。

注意：
要打开输出语句的设置，默认为关闭。

1
set serverout on;

实例2：定义变量并动态赋值变量

1
2
3
4
5
6
7
8
declare
	pesex char(3) := '男';
    pename varchar2(10);
begin
	dbms_output.put_line('hello world:' || pesex);
    select ename into pename from emp where empno = 7499;
    dbms_output.put_line('hello world:' || pesex || '---' || pename);
end;

输出结果：

1
2
3
4
hello world:男 
hello world:男 ---ALLEN

PL/SQL 过程已成功完成。

但此例是我们不常用并且是不推荐使用的，由此引出在PLSQL中对变量的推荐设置。

PLSQL的变量推荐设置

PLSQL的变量分为两类：

  • 引用型变量。比如：pename emp.ename%type;即变量pename与emp表中的ename字段的类型保持一致，此类型包括字段类型和字段长度等。以便于之后如果emp表的字段发生改变而导致PLSQL无法使用。
  • 记录型变量。比如：emp_info emp%rowtype;即变量emp_info与emp表中的所有行的类型都保持一致。类似于Java的对象：Person per = new Person(1, 'zs', 28, 175cm, 65kg);，可以用于同时保存多个变量值。

实例3：引用型变量与记录型变量

1
2
3
4
5
6
declare
	emp_info emp%rowtype;
begin
    select * into emp_info from emp where empno = 7499;
    dbms_output.put_line(emp_info.empno || '---' || emp_info.ename || '---' || emp_info.job);
end;

输出结果：

1
2
3
7499---ALLEN---SALESMAN

PL/SQL 过程已成功完成。

PLSQL的选择结构：if语句

有三种语法：

  • 第一种

    1
    2
    if 条件 then ... ;
    end if;

  • 第二种

    1
    2
    3
    if 条件 then ...;
    else ...
    end if;

  • 第三种

    1
    2
    3
    4
    if 条件 then ...;
    elsif 条件 then ...;
    else ...;
    end if ;

实例4：选择结构

pnum 为 1 时：

1
2
3
4
5
6
7
8
declare
	pnum number := 1;
begin
    if pnum = 1 then dbms_output.put_line('一');
    elsif pnum = 2 then dbms_output.put_line('二');
    else dbms_output.put_line('其他');
    end if;
end;

输出结果：

1
2
3
一

PL/SQL 过程已成功完成。

pnum 为 3 时：

1
2
3
4
5
6
7
8
declare
	pnum number := 3;
begin
    if pnum = 1 then dbms_output.put_line('一');
    elsif pnum = 2 then dbms_output.put_line('二');
    else dbms_output.put_line('其他');
    end if;
end;

输出结果：

1
2
3
其他

PL/SQL 过程已成功完成。

PLSQL的循环结构

  • for语句

    1
    2
    3
    4
    for i in 开始 .. 结束
    loop
    ...
    end loop;

  • while语句

    1
    2
    3
    4
    while 条件
    loop 
    ...
    end loop;

  • do while语句

    1
    2
    3
    4
    loop 
    ...
       exit when 条件;
    end loop;

注意：
PLSQL的do while语句与Java的do while语句的判断条件是相反的！
比如 i大于5是结束。PLSQL表示为 exit when i > 5;而Java表示为 while(i <= 5)。

实例5：循环结构

  • for语句
    输出数字1—5。

    1
    2
    3
    4
    5
    6
    7
    8
    declare
    	
    begin
        for i in 1 .. 5
        loop
            dbms_output.put_line(i);
        end loop;
    end;
输出结果：


1
2
3
4
5
6
7
1
2
3
4
5

PL/SQL 过程已成功完成。

  • do while语句
    计算1—5之和。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    declare
    	psum number := 0;
        i number := 1;
    begin
        loop
            psum := psum + i;
            i := i + 1;
            exit when i > 5;
        end loop;
        dbms_output.put_line(psum);
    end;
输出结果：


1
2
3
15

PL/SQL 过程已成功完成。

游标（光标）

游标的基本概念

比如：select ename from emp;，输出结果为：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
ENAME
----------
xxx
xxx
x
x
李四
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
seq

已选择20行。

现在ename有一堆数据，怎么保存呢？之前的变量pname只能保存一个或一行数据，而多行数据或一大推数据该怎么保存呢？由此引出：

游标（光标）cursor：用来保存一个集合的数据。

游标的创建

语法：

1
2
3
cursor 游标名（参数列表）
is
select ...

游标的四个属性

  • %isopen：
    游标使用之前必须打开。用来判断游标是否开启。

  • %rowcount：
    用来判断游标已经读取了多少行数据。

  • %found：
    判断当前行数据的下一行数据是否存在。

  • %notfound：
    判断当前行数据的下一行数据是否存在。

实例6：游标的基本使用

  • 查询并打印全体员工的姓名、薪水，如图：

    M2x1AJ.png

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    set serveroutput on;
    declare
        cursor cemp is select ename, sal from emp;
        pename emp.ename%type;
        psal emp.sal%type;
    begin
        open cemp;--1.打开游标。
        loop    --2.循环 准备获取每一行数据。
            fetch cemp into pename, psal;   --3.一行一行获取游标的值。
            exit when cemp%notfound;
            dbms_output.put_line(pename || '工资是：' || psal);
        end loop;    
        close cemp;--关闭游标。
    end;
输出结果：


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
xxx工资是：999
xxx工资是：999
x工资是：9999
x工资是：9999
李四工资是：
SMITH工资是：800
ALLEN工资是：1600
WARD工资是：1250
JONES工资是：2975
MARTIN工资是：1250
BLAKE工资是：2850
CLARK工资是：2450
SCOTT工资是：3000
KING工资是：5000
TURNER工资是：1500
ADAMS工资是：1100
JAMES工资是：950
FORD工资是：3000
MILLER工资是：1300
seq工资是：

PL/SQL 过程已成功完成。



> 注意：
> 游标有几个字段，就定义几个变量进行保存。
> 一行一行的取数据，必然需要使用循环。

  • 按照职位进行涨工资，PRESIDENT职位涨薪1000元，MANAGER职位涨薪800元，其他职位涨薪400元。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    set serveroutput on;
    declare
        cursor cemp is select empno, job from emp;
        pempno emp.empno%type;
        pjob emp.job%type;
    begin
        open cemp;--打开游标。
        loop
            fetch cemp into pempno, pjob;
            exit when cemp%notfound;
            --判断职位
            if pjob = 'PRESIDENT'
                then update emp set sal = sal + 1000 where empno = pempno;
            elsif pjob = 'MANAGER'
                then update emp set sal = sal + 800 where empno = pempno;
            else 
                update emp set sal = sal + 400 where empno = pempno;
            end if;
        end loop;
        close cemp;--关闭游标。
    end;
    commit;
> 注意：
> Oralce 的ACID默认级别是：`read commit`即一边（终端）不提交，另一个访问不到。

  • 查询某个部门的员工姓名，带参数的光标。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    declare
        cursor cename(dno number) is select ename from emp where deptno = dno;
        pename emp.ename%type;
    begin
        open cename(20);
        loop
            fetch cename into pename;
            exit when cename%notfound;
            dbms_output.put_line(pename);
        end loop;
        close cename;
    end;
输出结果：


1
2
3
4
5
6
SMITH
ADAMS
SCOTT
JONES

PL/SQL 过程已成功完成。

游标的例外（异常）

  • 系统例外：

    • no_data_found：没有该数据。
    • too_many_rows：变量无法保存太多的数据。
    • zero_divide：数学例外。
    • value_error：算术或转换错误。
    • timeout_on_resource：资源等待超时。（分布式数据库）

  • 自定义例外：

    就像Java一样，自建一个异常，如果满足条件则抛出异常，然后用try...catch进行捕获。比如：

    1
    2
    3
    4
    5
    6
    7

    自建异常：MyExc

    try{
        if(...) throws new MyExc();
    }catch(){...}
    catch(){...}

实例7：游标的例外

系统例外

1
2
3
4
5
6
7
8
9
10
declare
    pnum number;
begin
    pnum := 1/0;
    
    exception
        when zero_divide then dbms_output.put_line('0不能作为除数');
        when value_error then dbms_output.put_line('行数太多');
        when others then dbms_output.put_line('其他例外');
end;

输出结果：

1
2
3
0不能作为除数

PL/SQL 过程已成功完成。

自定义例外

1
2
3
4
5
6
7
8
9
declare
    myexc exception;
    pnum number := 1;
begin
    if pnum = 1 then raise myexc;
    end if;
    exception
        when myexc then dbms_output.put_line('自定义例外');
end;

输出结果：

1
2
3
自定义例外

PL/SQL 过程已成功完成。

是否存在问题

是否存在编号为50的部门，如果不存在，抛出一个例外；如果存在，将该部门的员工姓名打印出来。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
set serveroutput on;
declare
    cursor cemp(dno number) is select ename from emp where deptno = dno;
    pename emp.ename%type;
    no_emp_found exception;--自定义例外
begin
    open cemp(50);
    fetch cemp into pename;--先获取一行游标数据，才能进行判断
    if cemp%notfound then raise no_emp_found;
    else 
        loop
            exit when cemp%notfound;
            fetch cemp into pename;
            dbms_output.put_line(pename);
        end loop;
    end if;
    exception when no_emp_found then dbms_output.put_line('自定义例外，没有此号部门。');
    close cemp;
end;

输出结果：

1
2
3
自定义例外，没有此号部门。

PL/SQL 过程已成功完成。

实例8：游标的使用场景

统计每年的入职人数

  • 思考普通SQL的做法：

    1
    2
    3
    4
    5
    6
    7
    8
    SQL> select to_char(hiredate, 'yyyy'), count(*) from emp group by to_char(hiredate, 'yyyy');

    TO_C   COUNT(*)
    ---- ----------
    1987          2
    1980          1
    1982          1
    1981         10

  • 思考Java的做法：

    将每个人的数据获取出来，判断入职年份，如果是1980年，则计数+1；如果是1981年，则计数+1，以此类推。

  • 将Java思想转为PLSQL的做法：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    set serveroutput on;
    declare
        cursor cemp is select to_char(hiredate, 'yyyy') from emp;
        phiredate varchar(4);--判断年份，以备计数。
        count80 number := 0;
        count81 number := 0;
        count82 number := 0;
        count87 number := 0;
    begin
        open cemp;
        loop
            fetch cemp into phiredate;
            exit when cemp%notfound;
            if phiredate = '1980' then count80 := count80 + 1;
            elsif phiredate = '1981' then count81 := count81 + 1;
            elsif phiredate = '1982' then count82 := count82 + 1;
            else count87 := count87 + 1;
            end if;
        end loop;
        dbms_output.put_line('1980 ' || count80);
        dbms_output.put_line('1981 ' || count81);
        dbms_output.put_line('1982 ' || count82);
        dbms_output.put_line('1987 ' || count87);
        dbms_output.put_line('总人数 ' || ( count80 + count81 + count82 + count87 ) );
        close cemp;    
    end;

    输出结果：

    1
    2
    3
    4
    5
    6
    7
    1980 1
    1981 10
    1982 1
    1987 8
    总人数 20

    PL/SQL 过程已成功完成。

涨工资问题

每人涨10%的工资，但是按入职时间顺序涨工资，且涨后的全体员工的总工资不能超过5万，超过则剩下的员工就不涨工资了。计算需要涨工资的人的个数以及涨后的工资总额。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
set serveroutput on;
declare
    cursor cemp is select empno, sal from emp order by hiredate asc;
    pempno emp.empno%type;
    psal emp.sal%type;
    countEmp number := 0;
    salTotal number := 0;
begin
    open cemp;
    loop
        fetch cemp into pempno, psal;
        exit when cemp%notfound;--全体员工都统计完毕了
        if salTotal + psal * 1.1 < 50000
            then update emp set sal = sal * 1.1 where empno = pempno;
            countEmp := countEmp + 1;
            salTotal := salTotal + psal * 1.1;
        else 
            dbms_output.put_line('人数' || countEmp || '--- 涨后的总额' || salTotal);
            exit;
        end if;
    end loop;
    close cemp;    
end;

输出结果：

1
2
3
人数14--- 涨后的总额40067.5

PL/SQL 过程已成功完成。

统计各部门的工资情况

格式如下

1
2
部门编号    工资小于2000的人数     工资在2000至4000的人数     工资大于4000的人数     各部门工资总额
pdeptno		    count1		    	  count2			       count3		     salTotal

输出结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
set serveroutput on;
declare
    --各个部门编号
    cursor cdept is select deptno from dept;
    pdeptno dept.deptno%type;
    --部门中所有员工的工资
    cursor cemp(dno number) is select sal from emp where deptno = dno;
    psal emp.sal%type;
    --统计各部门工资分布的人数
    count1 number;
    count2 number;
    count3 number;
    --统计各部门工资总额
    salTotal number;
begin
    open cdept;
    --外层循环：遍历所有的部门编号
    loop
        fetch cdept into pdeptno;
        exit when cdept%notfound;
        count1 := 0;
        count2 := 0;
        count3 := 0;
        select sum(sal) into salTotal from emp where deptno = pdeptno;
        open cemp(pdeptno);
        --内层循环：遍历某个部门的所有员工的工资
        loop
            fetch cemp into psal;
            exit when cemp%notfound;
            if psal < 2000 then count1 := count1 + 1;
            elsif psal >=2000 and psal < 4000 then count2 := count2 + 1;
            elsif psal >= 4000 then count3 := count3 + 1;
            end if;
        end loop;
        close cemp;
        dbms_output.put_line(pdeptno || '号部门的工资总额是：' || salTotal || ' 工资小于2000的人数是：' || count1 || ' 工资在2000至4000的人数是：' || count2 || ' 工资大于4000的人数是：' || count3 );
    end loop;
    close cdept;
end;

感谢您花时间阅读我的文章！

如果本文对您有用，请您推广我的 Hexo 博客。谢谢！

https://liuandcolin630.github.io/

赏

Colin谢谢您的支持！

扫一扫，分享到微信

微信分享二维码